﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BM008_OS9_IQXEL
{
    public static class TestDataRepository
    {
        private static string _connectionString;
        private static string _dbPath;

        // 静态构造函数，初始化数据库连接
        static TestDataRepository()
        {
            _dbPath = "TestData.db";
            _connectionString = $"Data Source={_dbPath};Version=3;";
            InitializeDatabase();
        }

        private static void InitializeDatabase()
        {
            if (!File.Exists(_dbPath))
            {
                SQLiteConnection.CreateFile(_dbPath);
            }

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string createTableQuery = @"
                CREATE TABLE IF NOT EXISTS TestResults (
                    Id INTEGER PRIMARY KEY AUTOINCREMENT,
                    Address TEXT NOT NULL,
                    TestType TEXT NOT NULL,
                    TestItem TEXT NOT NULL,
                    TestValue TEXT,
                    Low TEXT,
                    Med TEXT,
                    High TEXT,
                    LL TEXT,
                    UL TEXT,
                    Unit TEXT,  -- 新增 Unit 字段
                    Result TEXT,
                    CreateTime DATETIME DEFAULT CURRENT_TIMESTAMP
                )";

                using (var command = new SQLiteCommand(createTableQuery, connection))
                {
                    command.ExecuteNonQuery();
                }
            }
        }

        // 同步插入方法
        public static void InsertTestData(TestData testData)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string insertQuery = @"
                INSERT INTO TestResults 
                (Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result)
                VALUES 
                (@Address, @TestType, @TestItem, @TestValue, @Low, @Med, @High, @LL, @UL, @Unit, @Result)";

                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    AddParameters(command, testData);
                    command.ExecuteNonQuery();
                }
            }
        }

        // 异步插入方法
        public static async Task InsertTestDataAsync(TestData testData)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                string insertQuery = @"
                INSERT INTO TestResults 
                (Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result)
                VALUES 
                (@Address, @TestType, @TestItem, @TestValue, @Low, @Med, @High, @LL, @UL, @Unit, @Result)";

                using (var command = new SQLiteCommand(insertQuery, connection))
                {
                    AddParameters(command, testData);
                    await command.ExecuteNonQueryAsync();
                }
            }
        }

        private static void AddParameters(SQLiteCommand command, TestData testData)
        {
            command.Parameters.AddWithValue("@Address", testData.Address);
            command.Parameters.AddWithValue("@TestType", testData.TestType);
            command.Parameters.AddWithValue("@TestItem", testData.TestItem);
            command.Parameters.AddWithValue("@TestValue", testData.TestValue ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Low", testData.Low ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Med", testData.Med ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@High", testData.High ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@LL", testData.LL ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@UL", testData.UL ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Unit", testData.Unit ?? (object)DBNull.Value); // 新增 Unit 参数
            command.Parameters.AddWithValue("@Result", testData.Result ?? (object)DBNull.Value);
        }

        // 同步查询方法
        public static List<TestData> GetTestDataByAddress(string address)
        {
            var results = new List<TestData>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                //        string query = @"
                //SELECT Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result, CreateTime
                //FROM TestResults 
                //WHERE Address = @Address 
                //ORDER BY CreateTime ASC, Id ASC";  // 修改排序：按创建时间升序，Id升序
                string query = @"
SELECT Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result, CreateTime
FROM TestResults 
WHERE Address = @Address"; // &#10060; 移除了 ORDER BY 子句
                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Address", address);

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            results.Add(ReadTestDataFromReader(reader));
                        }
                    }
                }
            }

            return results;
        }

        // 异步查询方法
        public static async Task<List<TestData>> GetTestDataByAddressAsync(string address)
        {
            var results = new List<TestData>();

            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                //        string query = @"
                //SELECT Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result, CreateTime
                //FROM TestResults 
                //WHERE Address = @Address 
                //ORDER BY CreateTime ASC, Id ASC";  // 修改排序：按创建时间升序，Id升序
                string query = @"
SELECT Address, TestType, TestItem, TestValue, Low, Med, High, LL, UL, Unit, Result, CreateTime
FROM TestResults 
WHERE Address = @Address"; // &#10060; 移除了 ORDER BY 子句

                using (var command = new SQLiteCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Address", address);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            results.Add(ReadTestDataFromReader(reader));
                        }
                    }
                }
            }

            return results;
        }
        // 通用的读取方法，使用 DbDataReader
        private static TestData ReadTestDataFromReader(System.Data.Common.DbDataReader reader)
        {
            return new TestData
            {
                Address = reader["Address"].ToString(),
                TestType = reader["TestType"].ToString(),
                TestItem = reader["TestItem"].ToString(),
                TestValue = reader.IsDBNull(reader.GetOrdinal("TestValue")) ? null : reader["TestValue"].ToString(),
                Low = reader.IsDBNull(reader.GetOrdinal("Low")) ? null : reader["Low"].ToString(),
                Med = reader.IsDBNull(reader.GetOrdinal("Med")) ? null : reader["Med"].ToString(),
                High = reader.IsDBNull(reader.GetOrdinal("High")) ? null : reader["High"].ToString(),
                LL = reader.IsDBNull(reader.GetOrdinal("LL")) ? null : reader["LL"].ToString(),
                UL = reader.IsDBNull(reader.GetOrdinal("UL")) ? null : reader["UL"].ToString(),
                Unit = reader.IsDBNull(reader.GetOrdinal("Unit")) ? null : reader["Unit"].ToString(), // 新增 Unit 读取
                Result = reader.IsDBNull(reader.GetOrdinal("Result")) ? null : reader["Result"].ToString(),
                CreateTime = Convert.ToDateTime(reader["CreateTime"])
            };
        }

        // 同步删除方法
        public static int DeleteTestDataByAddress(string address)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                connection.Open();

                string deleteQuery = "DELETE FROM TestResults WHERE Address = @Address";

                using (var command = new SQLiteCommand(deleteQuery, connection))
                {
                    command.Parameters.AddWithValue("@Address", address);
                    return command.ExecuteNonQuery();
                }
            }
        }

        // 异步删除方法
        public static async Task<int> DeleteTestDataByAddressAsync(string address)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();

                string deleteQuery = "DELETE FROM TestResults WHERE Address = @Address";

                using (var command = new SQLiteCommand(deleteQuery, connection))
                {
                    command.Parameters.AddWithValue("@Address", address);
                    return await command.ExecuteNonQueryAsync();
                }
            }
        }

        // 同步生成报告
        public static void GenerateHtmlReport(string address, string outputDirectory = "Reports")
        {
            var testDataList = GetTestDataByAddress(address);
            GenerateHtmlReportInternal(testDataList, address, outputDirectory);
        }

        // 异步生成报告
        public static async Task GenerateHtmlReportAsync(bool Result, string address)
        {
            var testDataList = await GetTestDataByAddressAsync(address);
            GenerateHtmlReportInternal(testDataList, address, Result ? $"{Directory.GetCurrentDirectory()}\\PASS" : $"{Directory.GetCurrentDirectory()}\\FAIL");
        }

        /// <summary>
        /// OK
        /// </summary>
        /// <param name="testDataList"></param>
        /// <param name="address"></param>
        /// <param name="outputDirectory"></param>
        private static void GenerateHtmlReportInternal_not(List<TestData> testDataList, string address, string outputDirectory)
        {
            if (!Directory.Exists(outputDirectory))
            {
                Directory.CreateDirectory(outputDirectory);
            }

            string fileName = $"{address}.html";
            string filePath = Path.Combine(outputDirectory, fileName);

            var htmlBuilder = new StringBuilder();

            // HTML头部
            htmlBuilder.AppendLine("<!DOCTYPE html>");
            htmlBuilder.AppendLine("<html lang='zh-CN'>");
            htmlBuilder.AppendLine("<head>");
            htmlBuilder.AppendLine("    <meta charset='UTF-8'>");
            htmlBuilder.AppendLine("    <meta name='viewport' content='width=device-width, initial-scale=1.0'>");
            htmlBuilder.AppendLine($"    <title>测试报告 - {address}</title>");
            htmlBuilder.AppendLine("    <style>");
            htmlBuilder.AppendLine("        body { font-family: Arial, sans-serif; margin: 20px; }");
            htmlBuilder.AppendLine("        table { width: 100%; border-collapse: collapse; margin-bottom: 30px; }");
            htmlBuilder.AppendLine("        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }");
            htmlBuilder.AppendLine("        th { background-color: #f2f2f2; font-weight: bold; }");
            htmlBuilder.AppendLine("        tr:nth-child(even) { background-color: #f9f9f9; }");
            htmlBuilder.AppendLine("        .pass { color: green; font-weight: bold; }");
            htmlBuilder.AppendLine("        .fail { color: red; font-weight: bold; }");
            htmlBuilder.AppendLine("        .na { color: gray; font-style: italic; }");
            htmlBuilder.AppendLine("        .header { text-align: center; margin-bottom: 20px; }");
            htmlBuilder.AppendLine("        .summary { margin: 20px 0; padding: 10px; background-color: #f5f5f5; border-radius: 5px; }");
            htmlBuilder.AppendLine("        .table-container { margin-bottom: 40px; }");
            htmlBuilder.AppendLine("        .table-title { background-color: #e8f4f8; padding: 10px; border-radius: 5px 5px 0 0; margin-bottom: 0; }");
            htmlBuilder.AppendLine("    </style>");
            htmlBuilder.AppendLine("</head>");
            htmlBuilder.AppendLine("<body>");

            // 标题
            htmlBuilder.AppendLine($"    <div class='header'>");
            htmlBuilder.AppendLine($"        <h1>TestReport</h1>");
            htmlBuilder.AppendLine($"        <p>GenerationTime: {DateTime.Now:yyyy-MM-dd HH:mm:ss}</p>");
            htmlBuilder.AppendLine($"    </div>");

            // 测试结果统计
            int totalTests = testDataList.Count;
            int passCount = testDataList.Count(t => t.Result?.ToUpper() == "PASS");
            int failCount = testDataList.Count(t => t.Result?.ToUpper() == "FAIL");
            int naCount = totalTests - passCount - failCount;

            htmlBuilder.AppendLine($"    <div class='summary'>");
            htmlBuilder.AppendLine($"        <h3>Test statistics</h3>");
            htmlBuilder.AppendLine($"        <p>Total number of tests: {totalTests}</p>");
            htmlBuilder.AppendLine($"        <p class='pass'>PASS: {passCount}</p>");
            htmlBuilder.AppendLine($"        <p class='fail'>FAIL: {failCount}</p>");
            htmlBuilder.AppendLine($"        <p class='na'>N/A: {naCount}</p>");
            htmlBuilder.AppendLine($"        <p>PassRate: {(totalTests > 0 ? (passCount * 100.0 / totalTests).ToString("F2") : "0")}%</p>");
            htmlBuilder.AppendLine($"    </div>");

            // 按测试类型分组显示，但保持原始插入顺序
            var groupedByType = testDataList.GroupBy(t => t.TestType);

            foreach (var group in groupedByType)
            {
                htmlBuilder.AppendLine("    <div class='table-container'>");

                //// 将数据排序：没有Low、Med、High的数据排在前面
                //var sortedGroup = group.OrderBy(t =>
                //    string.IsNullOrEmpty(t.Low) && string.IsNullOrEmpty(t.Med) && string.IsNullOrEmpty(t.High) ? 0 : 1)
                //    .ThenBy(t => t.TestItem);
                // 改为直接使用原始分组数据
                var sortedGroup = group; // 保持原始顺序
                // 动态确定需要显示的列
                bool showLow = group.Any(t => !string.IsNullOrEmpty(t.Low));
                bool showMed = group.Any(t => !string.IsNullOrEmpty(t.Med));
                bool showHigh = group.Any(t => !string.IsNullOrEmpty(t.High));
                bool showLL = group.Any(t => !string.IsNullOrEmpty(t.LL));
                bool showUL = group.Any(t => !string.IsNullOrEmpty(t.UL));
                bool showUnit = group.Any(t => !string.IsNullOrEmpty(t.Unit));

                htmlBuilder.AppendLine("    <table>");

                // 表格标题行（作为表格的第一行）
                htmlBuilder.AppendLine("        <thead>");
                htmlBuilder.AppendLine("            <tr>");
                htmlBuilder.AppendLine($"                <th colspan='{(8 + (showLow ? 1 : 0) + (showMed ? 1 : 0) + (showHigh ? 1 : 0) + (showLL ? 1 : 0) + (showUL ? 1 : 0))}' class='table-title'>");
                htmlBuilder.AppendLine($"                    TestType: {group.Key}");
                htmlBuilder.AppendLine("                </th>");
                htmlBuilder.AppendLine("            </tr>");
                htmlBuilder.AppendLine("            <tr>");
                htmlBuilder.AppendLine("                <th>TestItem</th>");
                htmlBuilder.AppendLine("                <th>TestValue</th>");

                if (showLow) htmlBuilder.AppendLine("                <th>Low</th>");
                if (showMed) htmlBuilder.AppendLine("                <th>Med</th>");
                if (showHigh) htmlBuilder.AppendLine("                <th>High</th>");
                if (showLL) htmlBuilder.AppendLine("                <th>Lower limit</th>");
                if (showUL) htmlBuilder.AppendLine("                <th>Upper limit</th>");
                if (showUnit) htmlBuilder.AppendLine("                <th>Unit</th>"); // 新增 Unit 列

                htmlBuilder.AppendLine("                <th>Result</th>");
                htmlBuilder.AppendLine("            </tr>");
                htmlBuilder.AppendLine("        </thead>");
                htmlBuilder.AppendLine("        <tbody>");

                foreach (var testData in sortedGroup)
                {
                    string resultClass = "na";
                    string resultText = "N/A";
                    string unitText = testData.Unit ?? "N/A"; // Unit 显示逻辑

                    if (!string.IsNullOrEmpty(testData.Result))
                    {
                        resultClass = testData.Result.ToUpper() == "PASS" ? "pass" : "fail";
                        resultText = testData.Result;
                    }

                    htmlBuilder.AppendLine("            <tr>");
                    htmlBuilder.AppendLine($"                <td>{testData.TestItem}</td>");
                    htmlBuilder.AppendLine($"                <td>{testData.TestValue ?? "-"}</td>");

                    if (showLow) htmlBuilder.AppendLine($"                <td>{testData.Low ?? "-"}</td>");
                    if (showMed) htmlBuilder.AppendLine($"                <td>{testData.Med ?? "-"}</td>");
                    if (showHigh) htmlBuilder.AppendLine($"                <td>{testData.High ?? "-"}</td>");
                    if (showLL) htmlBuilder.AppendLine($"                <td>{testData.LL ?? "-"}</td>");
                    if (showUL) htmlBuilder.AppendLine($"                <td>{testData.UL ?? "-"}</td>");
                    if (showUnit) htmlBuilder.AppendLine($"                <td class='na'>{unitText}</td>"); // Unit 列

                    htmlBuilder.AppendLine($"                <td class='{resultClass}'>{resultText}</td>");
                    htmlBuilder.AppendLine("            </tr>");
                }

                htmlBuilder.AppendLine("        </tbody>");
                htmlBuilder.AppendLine("    </table>");
                htmlBuilder.AppendLine("    </div>");
            }

            htmlBuilder.AppendLine("</body>");
            htmlBuilder.AppendLine("</html>");

            File.WriteAllText(filePath, htmlBuilder.ToString(), Encoding.UTF8);
        }
        /// <summary>
        /// 新格式 去除良率统计 OK
        /// </summary>
        /// <param name="testDataList"></param>
        /// <param name="address"></param>
        /// <param name="outputDirectory"></param>
        private static void GenerateHtmlReportInternal(List<TestData> testDataList, string address, string outputDirectory)
        {
            if (!Directory.Exists(outputDirectory))
            {
                Directory.CreateDirectory(outputDirectory);
            }

            string fileName = $"{address}.html";
            string filePath = Path.Combine(outputDirectory, fileName);

            var htmlBuilder = new StringBuilder();

            // HTML头部
            htmlBuilder.AppendLine("<!DOCTYPE html>");
            htmlBuilder.AppendLine("<html lang='zh-CN'>");
            htmlBuilder.AppendLine("<head>");
            htmlBuilder.AppendLine("    <meta charset='UTF-8'>");
            htmlBuilder.AppendLine("    <meta name='viewport' content='width=device-width, initial-scale=1.0'>");
            htmlBuilder.AppendLine($"    <title>测试报告 - {address}</title>");
            htmlBuilder.AppendLine("    <style>");
            htmlBuilder.AppendLine("        body { font-family: Arial, sans-serif; margin: 20px; }");
            htmlBuilder.AppendLine("        table { width: 100%; border-collapse: collapse; margin-bottom: 30px; }");
            htmlBuilder.AppendLine("        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }");
            htmlBuilder.AppendLine("        th { background-color: #f2f2f2; font-weight: bold; }");
            htmlBuilder.AppendLine("        tr:nth-child(even) { background-color: #f9f9f9; }");
            htmlBuilder.AppendLine("        .pass { color: green; font-weight: bold; }");
            htmlBuilder.AppendLine("        .fail { color: red; font-weight: bold; }");
            htmlBuilder.AppendLine("        .na { color: gray; font-style: italic; }");
            htmlBuilder.AppendLine("        .header { text-align: center; margin-bottom: 20px; }");
            htmlBuilder.AppendLine("        .table-container { margin-bottom: 40px; }");
            htmlBuilder.AppendLine("        .table-title { background-color: #e8f4f8; padding: 10px; border-radius: 5px 5px 0 0; margin-bottom: 0; }");
            htmlBuilder.AppendLine("    </style>");
            htmlBuilder.AppendLine("</head>");
            htmlBuilder.AppendLine("<body>");

            // 标题（移除统计信息）
            htmlBuilder.AppendLine($"    <div class='header'>");
            htmlBuilder.AppendLine($"        <h1>TestReport</h1>");
            htmlBuilder.AppendLine($"        <p>GenerationTime: {DateTime.Now:yyyy-MM-dd HH:mm:ss}</p>");
            htmlBuilder.AppendLine($"    </div>");

            // 按测试类型分组显示，保持原始插入顺序
            var groupedByType = testDataList.GroupBy(t => t.TestType);

            foreach (var group in groupedByType)
            {
                htmlBuilder.AppendLine("    <div class='table-container'>");

                // 保持原始顺序
                var sortedGroup = group;

                // 动态确定需要显示的列
                bool showLow = group.Any(t => !string.IsNullOrEmpty(t.Low));
                bool showMed = group.Any(t => !string.IsNullOrEmpty(t.Med));
                bool showHigh = group.Any(t => !string.IsNullOrEmpty(t.High));
                bool showLL = group.Any(t => !string.IsNullOrEmpty(t.LL));
                bool showUL = group.Any(t => !string.IsNullOrEmpty(t.UL));
                bool showUnit = group.Any(t => !string.IsNullOrEmpty(t.Unit));

                htmlBuilder.AppendLine("    <table>");

                // 表格标题行（作为表格的第一行）
                htmlBuilder.AppendLine("        <thead>");
                htmlBuilder.AppendLine("            <tr>");
                htmlBuilder.AppendLine($"                <th colspan='{(8 + (showLow ? 1 : 0) + (showMed ? 1 : 0) + (showHigh ? 1 : 0) + (showLL ? 1 : 0) + (showUL ? 1 : 0))}' class='table-title'>");
                htmlBuilder.AppendLine($"                    TestType: {group.Key}");
                htmlBuilder.AppendLine("                </th>");
                htmlBuilder.AppendLine("            </tr>");
                htmlBuilder.AppendLine("            <tr>");
                htmlBuilder.AppendLine("                <th>TestItem</th>");
                htmlBuilder.AppendLine("                <th>TestValue</th>");

                if (showLow) htmlBuilder.AppendLine("                <th>Low</th>");
                if (showMed) htmlBuilder.AppendLine("                <th>Med</th>");
                if (showHigh) htmlBuilder.AppendLine("                <th>High</th>");
                if (showLL) htmlBuilder.AppendLine("                <th>Lower limit</th>");
                if (showUL) htmlBuilder.AppendLine("                <th>Upper limit</th>");
                if (showUnit) htmlBuilder.AppendLine("                <th>Unit</th>");

                htmlBuilder.AppendLine("                <th>Result</th>");
                htmlBuilder.AppendLine("            </tr>");
                htmlBuilder.AppendLine("        </thead>");
                htmlBuilder.AppendLine("        <tbody>");

                foreach (var testData in sortedGroup)
                {
                    string resultClass = "na";
                    string resultText = "N/A";
                    string unitText = testData.Unit ?? "N/A";

                    if (!string.IsNullOrEmpty(testData.Result))
                    {
                        resultClass = testData.Result.ToUpper() == "PASS" ? "pass" : "fail";
                        resultText = testData.Result;
                    }

                    htmlBuilder.AppendLine("            <tr>");
                    htmlBuilder.AppendLine($"                <td>{testData.TestItem}</td>");
                    htmlBuilder.AppendLine($"                <td>{testData.TestValue ?? "-"}</td>");

                    if (showLow) htmlBuilder.AppendLine($"                <td>{testData.Low ?? "-"}</td>");
                    if (showMed) htmlBuilder.AppendLine($"                <td>{testData.Med ?? "-"}</td>");
                    if (showHigh) htmlBuilder.AppendLine($"                <td>{testData.High ?? "-"}</td>");
                    if (showLL) htmlBuilder.AppendLine($"                <td>{testData.LL ?? "-"}</td>");
                    if (showUL) htmlBuilder.AppendLine($"                <td>{testData.UL ?? "-"}</td>");
                    if (showUnit) htmlBuilder.AppendLine($"                <td class='na'>{unitText}</td>");

                    htmlBuilder.AppendLine($"                <td class='{resultClass}'>{resultText}</td>");
                    htmlBuilder.AppendLine("            </tr>");
                }

                htmlBuilder.AppendLine("        </tbody>");
                htmlBuilder.AppendLine("    </table>");
                htmlBuilder.AppendLine("    </div>");
            }

            htmlBuilder.AppendLine("</body>");
            htmlBuilder.AppendLine("</html>");

            File.WriteAllText(filePath, htmlBuilder.ToString(), Encoding.UTF8);
        }

    }

    public class TestData
    {
        public string Address { get; set; }
        public string TestType { get; set; }
        public string TestItem { get; set; }
        public string TestValue { get; set; }
        public string Low { get; set; }
        public string Med { get; set; }
        public string High { get; set; }
        public string LL { get; set; }
        public string UL { get; set; }
        public string Unit { get; set; } // 新增 Unit 属性
        public string Result { get; set; }
        public DateTime CreateTime { get; set; }
    }
}